﻿namespace SRM.DataObjects
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;

    /// <summary>
    /// Summary description for SRM_Professional
    /// </summary>
    [DataObjectAttribute(true)]
    public class SRM_Professional
    {

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetProfessionalMembership(string applicantKey)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format("SELECT * FROM SRM_ApplicantProfMembership_Tbl WHERE ApplicantKey = {0};", applicantKey);
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        } // public static DataTable GetProfessionalMembership(string applicantKey)

        [DataObjectMethodAttribute(DataObjectMethodType.Insert, true)]
        public static void InsertProfessionalMembership(string applicantKey, 
            string institution, string certification, string date_attained, string position_held)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "INSERT INTO SRM_ApplicantProfMembership_Tbl (ApplicantKey,ProfInstitution,ProfCertification,YearAttended,PositionHeld) VALUES (@ApplicantKey,@ProfInstitution,@ProfCertification,@YearAttended,@PositionHeld);";
                cmd.Parameters.AddWithValue("@ApplicantKey", applicantKey);
                cmd.Parameters.AddWithValue("@ProfInstitution", institution);
                cmd.Parameters.AddWithValue("@ProfCertification", certification);
                cmd.Parameters.AddWithValue("@YearAttended", date_attained);
                cmd.Parameters.AddWithValue("@PositionHeld", position_held);

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void InsertLanguageProficiency(string applicantKey, string language, string ability, string proficiency)

        [DataObjectMethodAttribute(DataObjectMethodType.Delete, true)]
        public static void DeleteProfessionalMembership(string profMembershipKey)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "DELETE FROM SRM_ApplicantProfMembership_Tbl WHERE ProfMembershipKey = @ProfMembershipKey;";
                cmd.Parameters.AddWithValue("@ProfMembershipKey", profMembershipKey);

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void InsertLanguageProficiency(string applicantKey, string language, string ability, string proficiency)

        [DataObjectMethodAttribute(DataObjectMethodType.Update, true)]
        public static void UpdateProfessionalMembership(string profMembershipKey,
            string institution, string certification, string date_attained, string position_held)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = @"
UPDATE  SRM_ApplicantProfMembership_Tbl 
SET     ProfInstitution = @ProfInstitution, 
        ProfCertification = @ProfCertification, 
        YearAttended = @YearAttended,
        PositionHeld = @PositionHeld
WHERE   ProfMembershipKey = @ProfMembershipKey;";

                cmd.Parameters.AddWithValue("@ProfMembershipKey", profMembershipKey);
                cmd.Parameters.AddWithValue("@ProfInstitution", institution);
                cmd.Parameters.AddWithValue("@ProfCertification", certification);
                cmd.Parameters.AddWithValue("@YearAttended", date_attained);
                cmd.Parameters.AddWithValue("@PositionHeld", position_held);

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void InsertLanguageProficiency(string applicantKey, string language, string ability, string proficiency)

    }
}